#!/usr/bin/env python
# -*- coding: utf-8 -*-

import cx_Oracle
import codecs
import string
import getpass
import sys

zf2 = lambda x: string.zfill(str(x), 2)

def prevent_header_cutoff( table
                         , conStr={'usr':'', 'pwd':'', 'dsn':''}):
    ''' create set commands for data export via sqlplus
        to prevent cut off of column names if wider than data
    '''
    con = cx_Oracle.connect(conStr['usr'], conStr['pwd'], conStr['dsn'])
    cur = con.cursor()

    s, t = string.split(table, '.')
    # oracle is case sensitive when querying ALL_TAB_COLUMNS
    s, t = s.upper(), t.upper()
    print "%s.%s" % (s, t)

    # query table details
    q = """
             select * from ALL_TAB_COLUMNS
              where OWNER = '%s'
                and TABLE_NAME = '%s'
              order by COLUMN_ID
              """ % (s, t)
    #print q
    cur.execute(q)
    results = cur.fetchall()[:]
    #
    for r in results:
        #print r
        c = r[2]
        c_data_type = r[3]
        c_data_length = r[6]
        c_char_decl_length = r[22]

        if c_char_decl_length in (None, ''): c_char_decl_length = 0
        maxlen = max(len(c), int(c_char_decl_length))
        if len(c) > c_char_decl_length and c_char_decl_length <> 0:
            #print c, cdt, cdl, cchdl
            print "COLUMN %s FORMAT A%s ;" % (c.upper(), maxlen)

    cur.close()
    con.close()


if 0:

    #import cx_Oracle
    #import pepe.p_sql.oracle_sqlplus as osp
    #import getpass

    ip = '127.0.0.1'    # tunneled to remote server or local connection
    #port = 1522         # shifted to tunneled 1522, on 1521 is local XE instance
    port = 1522
    sid = 'dtrslab3'
    dsn_tns = cx_Oracle.makedsn(ip, port, sid)

    #usr = 'vpresovsky'
    #pwd = '<...>'
    usr = raw_input("Input username: ")
    pwd = getpass.getpass()

    conString = {'usr': usr, 'pwd': pwd, 'dsn': dsn_tns}

    while 1:
        print '\n'
        st = raw_input("Input schema.table: ")
        #prevent_header_cutoff('GIS_RELEASE.a_result_zadost_full_v06', conString)
        try:
            prevent_header_cutoff(st, conString)
        except:
            for x in sys.exc_info()[:]:
                print str(x),
            

